I am indeed a novice, but I've done plenty of googling for an answer,
and haven't had any new ideas in a while, so I thought I'd ask...
I have what I would think is a very simple database. I use it to log
temperature probe readings. 8 different probes are recorded each
minute. There are two tables, one of which maps probe-id's to more
info about that probe, and another that holds all of the logged data,
as time, probe-id, and temp.
There is an index on the time column. Vacuum Analyze is run every night.
As you can see below, using an index doesn't seem to have any
significant impact on the query speed. Why not? I would expect a
b-tree index to be amazingly fast for this sort of query. Why doesn't
the index-scan stop once the filter threshold is crossed, since the
index is sorted (right?)?
-pmb
pooldb=> set enable_indexscan=false;
SET
pooldb=> explain analyze
pooldb-> select * from events where time > now() - '2
minutes'::reltime order by time desc;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
Sort (cost=8142.85..8286.17 rows=71659 width=20) (actual
time=15086.27..15086.32 rows=16 loops=1)
Sort Key: "time"
-> Seq Scan on events (cost=0.00..3519.76 rows=71659 width=20)
(actual time=15030.85..15032.23 rows=16 loops=1)
Filter: (("time")::timestamp with time zone > (now() -
'00:02'::interval))
Total runtime: 15086.55 msec
(5 rows)
pooldb=> set enable_indexscan=true;
SET
pooldb=> explain analyze
pooldb-> select * from events where time > now() - '2
minutes'::reltime order by time desc;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
Index Scan Backward using events_time_key on events
(cost=0.00..4556.74 rows=71659 width=20) (actual time=0.84..16495.36
rows=16 loops=1)
Filter: (("time")::timestamp with time zone > (now() - '00:02'::interval))
Total runtime: 16495.62 msec
(3 rows)
pooldb=> select count(time) from events;
count
--------
221974
(1 row)
